<!DOCTYPE html><html lang="zh-CN" data-theme="light"><head><meta charset="UTF-8"><meta http-equiv="X-UA-Compatible" content="IE=edge"><meta name="viewport" content="width=device-width, initial-scale=1.0,viewport-fit=cover"><title>MySQL基础篇 | XuanCode</title><meta name="author" content="xuanskeys"><meta name="copyright" content="xuanskeys"><meta name="format-detection" content="telephone=no"><meta name="theme-color" content="#ffffff"><meta name="description" content="数据库基本操作启动与停止1.第一种方式：1&gt;以管理员身份运行cmd   2&gt;在命令行窗口中输入: 123启动:net start mysql80停止:net stop mysql80   2.第二种方式:1&gt;Win+R快捷方式打开如下： ​    输入：services.msc   2&gt;找到MySQL80   3&gt;双击：   4&gt;这里我选择的是开机自启动 客户端">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL基础篇">
<meta property="og:url" content="http://xuanskeys.github.io/2025/04/20/%E6%95%B0%E6%8D%AE%E5%BA%93/index.html">
<meta property="og:site_name" content="XuanCode">
<meta property="og:description" content="数据库基本操作启动与停止1.第一种方式：1&gt;以管理员身份运行cmd   2&gt;在命令行窗口中输入: 123启动:net start mysql80停止:net stop mysql80   2.第二种方式:1&gt;Win+R快捷方式打开如下： ​    输入：services.msc   2&gt;找到MySQL80   3&gt;双击：   4&gt;这里我选择的是开机自启动 客户端">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="http://xuanskeys.github.io/image/person.jpg">
<meta property="article:published_time" content="2025-04-20T00:24:00.000Z">
<meta property="article:modified_time" content="2025-04-20T01:51:30.004Z">
<meta property="article:author" content="xuanskeys">
<meta property="article:tag" content="1">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="http://xuanskeys.github.io/image/person.jpg"><script type="application/ld+json">{
  "@context": "https://schema.org",
  "@type": "BlogPosting",
  "headline": "MySQL基础篇",
  "url": "http://xuanskeys.github.io/2025/04/20/%E6%95%B0%E6%8D%AE%E5%BA%93/",
  "image": "http://xuanskeys.github.io/image/person.jpg",
  "datePublished": "2025-04-20T00:24:00.000Z",
  "dateModified": "2025-04-20T01:51:30.004Z",
  "author": [
    {
      "@type": "Person",
      "name": "xuanskeys",
      "url": "http://xuanskeys.github.io/"
    }
  ]
}</script><link rel="shortcut icon" href="/image/%E5%AF%BF%E5%8F%B8.png"><link rel="canonical" href="http://xuanskeys.github.io/2025/04/20/%E6%95%B0%E6%8D%AE%E5%BA%93/index.html"><link rel="preconnect" href="//cdn.jsdelivr.net"/><link rel="preconnect" href="//busuanzi.ibruce.info"/><link rel="stylesheet" href="/css/index.css"><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free/css/all.min.css"><script>
    (() => {
      
    const saveToLocal = {
      set: (key, value, ttl) => {
        if (!ttl) return
        const expiry = Date.now() + ttl * 86400000
        localStorage.setItem(key, JSON.stringify({ value, expiry }))
      },
      get: key => {
        const itemStr = localStorage.getItem(key)
        if (!itemStr) return undefined
        const { value, expiry } = JSON.parse(itemStr)
        if (Date.now() > expiry) {
          localStorage.removeItem(key)
          return undefined
        }
        return value
      }
    }

    window.btf = {
      saveToLocal,
      getScript: (url, attr = {}) => new Promise((resolve, reject) => {
        const script = document.createElement('script')
        script.src = url
        script.async = true
        Object.entries(attr).forEach(([key, val]) => script.setAttribute(key, val))
        script.onload = script.onreadystatechange = () => {
          if (!script.readyState || /loaded|complete/.test(script.readyState)) resolve()
        }
        script.onerror = reject
        document.head.appendChild(script)
      }),
      getCSS: (url, id) => new Promise((resolve, reject) => {
        const link = document.createElement('link')
        link.rel = 'stylesheet'
        link.href = url
        if (id) link.id = id
        link.onload = link.onreadystatechange = () => {
          if (!link.readyState || /loaded|complete/.test(link.readyState)) resolve()
        }
        link.onerror = reject
        document.head.appendChild(link)
      }),
      addGlobalFn: (key, fn, name = false, parent = window) => {
        if (!false && key.startsWith('pjax')) return
        const globalFn = parent.globalFn || {}
        globalFn[key] = globalFn[key] || {}
        globalFn[key][name || Object.keys(globalFn[key]).length] = fn
        parent.globalFn = globalFn
      }
    }
  
      
      const activateDarkMode = () => {
        document.documentElement.setAttribute('data-theme', 'dark')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#0d0d0d')
        }
      }
      const activateLightMode = () => {
        document.documentElement.setAttribute('data-theme', 'light')
        if (document.querySelector('meta[name="theme-color"]') !== null) {
          document.querySelector('meta[name="theme-color"]').setAttribute('content', '#ffffff')
        }
      }

      btf.activateDarkMode = activateDarkMode
      btf.activateLightMode = activateLightMode

      const theme = saveToLocal.get('theme')
    
          theme === 'dark' ? activateDarkMode() : theme === 'light' ? activateLightMode() : null
        
      
      const asideStatus = saveToLocal.get('aside-status')
      if (asideStatus !== undefined) {
        document.documentElement.classList.toggle('hide-aside', asideStatus === 'hide')
      }
    
      
    const detectApple = () => {
      if (/iPad|iPhone|iPod|Macintosh/.test(navigator.userAgent)) {
        document.documentElement.classList.add('apple')
      }
    }
    detectApple()
  
    })()
  </script><script>const GLOBAL_CONFIG = {
  root: '/',
  algolia: undefined,
  localSearch: {"path":"/search.xml","preload":false,"top_n_per_article":1,"unescape":false,"languages":{"hits_empty":"未找到符合您查询的内容：${query}","hits_stats":"共找到 ${hits} 篇文章"}},
  translate: undefined,
  highlight: {"plugin":"highlight.js","highlightCopy":true,"highlightLang":true,"highlightHeightLimit":false,"highlightFullpage":false,"highlightMacStyle":false},
  copy: {
    success: '复制成功',
    error: '复制失败',
    noSupport: '浏览器不支持'
  },
  relativeDate: {
    homepage: false,
    post: false
  },
  runtime: '',
  dateSuffix: {
    just: '刚刚',
    min: '分钟前',
    hour: '小时前',
    day: '天前',
    month: '个月前'
  },
  copyright: undefined,
  lightbox: 'null',
  Snackbar: undefined,
  infinitegrid: {
    js: 'https://cdn.jsdelivr.net/npm/@egjs/infinitegrid/dist/infinitegrid.min.js',
    buttonText: '加载更多'
  },
  isPhotoFigcaption: false,
  islazyloadPlugin: false,
  isAnchor: false,
  percent: {
    toc: true,
    rightside: false,
  },
  autoDarkmode: false
}</script><script id="config-diff">var GLOBAL_CONFIG_SITE = {
  title: 'MySQL基础篇',
  isHighlightShrink: false,
  isToc: true,
  pageType: 'post'
}</script><link rel="stylesheet" href="/styles/main.css"><script src="/styles/fish.js"></script><script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.min.js"></script><meta name="generator" content="Hexo 7.3.0"></head><body><div id="loading-box"><div class="loading-left-bg"></div><div class="loading-right-bg"></div><div class="spinner-box"><div class="configure-border-1"><div class="configure-core"></div></div><div class="configure-border-2"><div class="configure-core"></div></div><div class="loading-word">加载中...</div></div></div><script>(()=>{
  const $loadingBox = document.getElementById('loading-box')
  const $body = document.body
  const preloader = {
    endLoading: () => {
      $body.style.overflow = ''
      $loadingBox.classList.add('loaded')
    },
    initLoading: () => {
      $body.style.overflow = 'hidden'
      $loadingBox.classList.remove('loaded')
    }
  }

  preloader.initLoading()
  window.addEventListener('load', preloader.endLoading)

  if (false) {
    btf.addGlobalFn('pjaxSend', preloader.initLoading, 'preloader_init')
    btf.addGlobalFn('pjaxComplete', preloader.endLoading, 'preloader_end')
  }
})()</script><div id="sidebar"><div id="menu-mask"></div><div id="sidebar-menus"><div class="avatar-img text-center"><img src="/image/person.jpg" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/></div><div class="site-data text-center"><a href="/archives/"><div class="headline">文章</div><div class="length-num">10</div></a><a href="/tags/"><div class="headline">标签</div><div class="length-num">1</div></a><a href="/categories/"><div class="headline">分类</div><div class="length-num">5</div></a></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><span class="site-page group"><i class="fa-fw fa fa-heartbeat"></i><span> 清单</span><i class="fas fa-chevron-down"></i></span><ul class="menus_item_child"><li><a class="site-page child" href="/music/"><i class="fa-fw fas fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/Gallery/"><i class="fa-fw fas fa-images"></i><span> 照片</span></a></li><li><a class="site-page child" href="/movies/"><i class="fa-fw fas fa-video"></i><span> 电影</span></a></li></ul></div></div></div></div><div class="post" id="body-wrap"><header class="post-bg" id="page-header" style="background-image: url(/image/background.jpg);"><nav id="nav"><span id="blog-info"><a class="nav-site-title" href="/"><span class="site-name">XuanCode</span></a><a class="nav-page-title" href="/"><span class="site-name">MySQL基础篇</span></a></span><div id="menus"><div id="search-button"><span class="site-page social-icon search"><i class="fas fa-search fa-fw"></i><span> 搜索</span></span></div><div class="menus_items"><div class="menus_item"><a class="site-page" href="/"><i class="fa-fw fas fa-home"></i><span> 首页</span></a></div><div class="menus_item"><a class="site-page" href="/archives/"><i class="fa-fw fas fa-archive"></i><span> 时间轴</span></a></div><div class="menus_item"><a class="site-page" href="/tags/"><i class="fa-fw fas fa-tags"></i><span> 标签</span></a></div><div class="menus_item"><a class="site-page" href="/categories/"><i class="fa-fw fas fa-folder-open"></i><span> 分类</span></a></div><div class="menus_item"><span class="site-page group"><i class="fa-fw fa fa-heartbeat"></i><span> 清单</span><i class="fas fa-chevron-down"></i></span><ul class="menus_item_child"><li><a class="site-page child" href="/music/"><i class="fa-fw fas fa-music"></i><span> 音乐</span></a></li><li><a class="site-page child" href="/Gallery/"><i class="fa-fw fas fa-images"></i><span> 照片</span></a></li><li><a class="site-page child" href="/movies/"><i class="fa-fw fas fa-video"></i><span> 电影</span></a></li></ul></div></div><div id="toggle-menu"><span class="site-page"><i class="fas fa-bars fa-fw"></i></span></div></div></nav><div id="post-info"><h1 class="post-title">MySQL基础篇</h1><div id="post-meta"><div class="meta-firstline"><span class="post-meta-date"><i class="far fa-calendar-alt fa-fw post-meta-icon"></i><span class="post-meta-label">发表于</span><time class="post-meta-date-created" datetime="2025-04-20T00:24:00.000Z" title="发表于 2025-04-20 08:24:00">2025-04-20</time><span class="post-meta-separator">|</span><i class="fas fa-history fa-fw post-meta-icon"></i><span class="post-meta-label">更新于</span><time class="post-meta-date-updated" datetime="2025-04-20T01:51:30.004Z" title="更新于 2025-04-20 09:51:30">2025-04-20</time></span></div><div class="meta-secondline"><span class="post-meta-separator">|</span><span class="post-meta-pv-cv" id="" data-flag-title=""><i class="far fa-eye fa-fw post-meta-icon"></i><span class="post-meta-label">浏览量:</span><span id="busuanzi_value_page_pv"><i class="fa-solid fa-spinner fa-spin"></i></span></span></div></div></div></header><main class="layout" id="content-inner"><div id="post"><article class="container post-content" id="article-container"><h1><span id="数据库基本操作">数据库基本操作</span></h1><h2><span id="启动与停止">启动与停止</span></h2><h3><span id="1第一种方式">1.第一种方式：</span></h3><p>1&gt;以管理员身份运行cmd</p>
<p><img src="/./../images/ac080b10ac8650e1bf5e67a4a1a75103.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<p>2&gt;在命令行窗口中输入:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">启动:net <span class="keyword">start</span> mysql80</span><br><span class="line"></span><br><span class="line">停止:net stop mysql80</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="2第二种方式">2.第二种方式:</span></h3><p>1&gt;Win+R快捷方式打开如下：</p>
<p>​    输入：services.msc</p>
<p><img src="/./../images/6fc1b7d2c1fdcc7b1a94719343811175.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<p>2&gt;找到MySQL80</p>
<p><img src="/./../images/ea8abd1cafd0e33732a76753c906cd3c.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<p>3&gt;双击：</p>
<p><img src="/./../images/2a01f3f2bce1cbfc19f29ab88a20bd61.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<p>4&gt;这里我选择的是开机自启动</p>
<h1><span id><img src="/./../images/7cbd89b62b69a93c874143ca2ae5c8b0.png" alt="img"><img src="" alt="点击并拖拽以移动"></span></h1><h2><span id="客户端连接">客户端连接</span></h2><h3><span id="1第一种方式通过mysql提供的客户端命令行工具">1.第一种方式：通过MySQL提供的客户端命令行工具</span></h3><p><img src="/./../images/b1fb09eb5076df7a20e20fbe61c7a3d2.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<p><img src="/./../images/b81ba769770f4205354d6d004f2cbbac.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<h3><span id="2第二种方式通过命令行工具执行命令">2.第二种方式：通过命令行工具执行命令</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">mysql [<span class="operator">-</span>h <span class="number">127.0</span><span class="number">.0</span><span class="number">.1</span>] [<span class="operator">-</span>P <span class="number">3306</span>] <span class="operator">-</span>u 用户 <span class="operator">-</span>p</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>注意：</strong></p>
<p>1.[]中可省略</p>
<p>2.使用这种方式时，需要配置PATH环境变量</p>
<p><img src="/./../images/c683121a0f127ba426d1de530b2e2dd2.png" alt="img"><img src="" alt="点击并拖拽以移动"><img src="/./../images/9ba35c53decf1b4f210059bb0290dd2d.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<h1><span id="sql">SQL</span></h1><p><img src="/./../images/733b17b5bb882f7d80a76e4a28bd69b5.jpeg" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<h2><span id="1ddl数据定义语言">1.DDL(数据定义语言)</span></h2><h3><span id="数据库操作">数据库操作</span></h3><h4><span id="查询所有数据库">查询所有数据库:</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> databases;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="查询当前数据库">查询当前数据库:</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> database();</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="创建数据库">创建数据库:</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> database [if <span class="keyword">not</span> <span class="keyword">exists</span>] 数据库名 [<span class="keyword">default</span> charset 字符集] [<span class="keyword">collate</span> 排序规则];</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="删除数据库">删除数据库:</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> database [if <span class="keyword">exists</span>] 数据库名;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="使用数据库">使用数据库:</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line">use 数据库名;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="表操作">表操作</span></h3><h4><span id="查询">查询：</span></h4><h5><span id="查询当前数据库所有表">查询当前数据库所有表：</span></h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> tables;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h5><span id="查询表结构">查询表结构:</span></h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">desc</span> 表名;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h5><span id="查询指定表的建表语句">查询指定表的建表语句:</span></h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> <span class="keyword">create table</span> 表名;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="创建">创建：</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create table</span> 表名(</span><br><span class="line"></span><br><span class="line">        字段<span class="number">1</span> 字段<span class="number">1</span>类型[comment <span class="string">&#x27;注释&#x27;</span>],</span><br><span class="line"></span><br><span class="line">        字段<span class="number">2</span> 字段<span class="number">2</span>类型[comment <span class="string">&#x27;注释&#x27;</span>],</span><br><span class="line"></span><br><span class="line">        字段<span class="number">3</span> 字段<span class="number">3</span>类型[comment <span class="string">&#x27;注释&#x27;</span>],</span><br><span class="line"></span><br><span class="line">        .......</span><br><span class="line"></span><br><span class="line">        字段n 字段n类型[comment <span class="string">&#x27;注释&#x27;</span>]</span><br><span class="line"></span><br><span class="line">) [comment <span class="string">&#x27;注释&#x27;</span>];       </span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="修改">修改：</span></h4><h5><span id="添加字段">添加字段：</span></h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter table</span> 表名 <span class="keyword">add</span> 字段名 类型(长度) [comment <span class="string">&#x27;注释&#x27;</span>] [约束];</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h5><span id="修改数据类型">修改数据类型：</span></h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter table</span> 表名 modify 字段名 新数据类型(长度);</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h5><span id="修改表名">修改表名：</span></h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter table</span> 表名 rename <span class="keyword">to</span> 新表名;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="删除">删除：</span></h4><h5><span id="删除表">删除表：</span></h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">table</span> [if <span class="keyword">exists</span>] 表名;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h5><span id="删除指定表并重新创建该表">删除指定表并重新创建该表：</span></h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">truncate</span> <span class="keyword">table</span> 表名;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<hr>
<h2><span id="2dml数据操作语言">2.DML(数据操作语言)</span></h2><h3><span id="添加数据insert">添加数据（insert）</span></h3><h4><span id="给指定字段添加数据">给指定字段添加数据：</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert into</span> 表名 (字段<span class="number">1</span>，字段<span class="number">2.</span>.....) <span class="keyword">values</span>(值<span class="number">1</span>， 值<span class="number">2.</span>.....);</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="给全部字段添加数据">给全部字段添加数据：</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert into</span> 表名 <span class="keyword">values</span>(值<span class="number">1</span>， 值<span class="number">2.</span>.....);</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="批量添加数据">批量添加数据：</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert into</span> 表名 (字段<span class="number">1</span>，字段<span class="number">2.</span>.....) <span class="keyword">values</span>(值<span class="number">1</span>， 值<span class="number">2.</span>.....),(值<span class="number">1</span>， 值<span class="number">2.</span>.....),(值<span class="number">1</span>， 值<span class="number">2.</span>.....);</span><br><span class="line"></span><br><span class="line"><span class="keyword">insert into</span> 表名 <span class="keyword">values</span>(值<span class="number">1</span>， 值<span class="number">2.</span>.....),(值<span class="number">1</span>， 值<span class="number">2.</span>.....),(值<span class="number">1</span>， 值<span class="number">2.</span>.....);</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>注意：</strong></p>
<blockquote>
<p>​    1.插入数据时要按注意顺序</p>
<p>​    2.字符串和日期型数据应该包含在引号中</p>
<p>​    3.插入的数据大小要合法</p>
</blockquote>
<h3><span id="修改数据update">修改数据（update）</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">update</span> 表名 <span class="keyword">set</span> 字段<span class="number">1</span><span class="operator">=</span>值<span class="number">1</span>，字段<span class="number">2</span><span class="operator">=</span>值<span class="number">2.</span>.....[<span class="keyword">where</span> 条件];</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>注意：</strong></p>
<blockquote>
<p>​    如果没有条件，则会修改整张表</p>
</blockquote>
<h3><span id="删除数据delete">删除数据（delete）</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">delete</span> <span class="keyword">from</span> 表名 [<span class="keyword">where</span> 条件]</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>注意：</strong></p>
<blockquote>
<p>​    1.如果没有条件，则会删除整张表的数据</p>
<p>​    2.delete不能删除某一字段的值</p>
</blockquote>
<hr>
<h2><span id="3dql数据查询语言">3.DQL(数据查询语言)</span></h2><h3><span id="编写顺序">编写顺序：</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表</span><br><span class="line"></span><br><span class="line"><span class="keyword">from</span> 表名列表</span><br><span class="line"></span><br><span class="line"><span class="keyword">where</span> 条件列表</span><br><span class="line"></span><br><span class="line"><span class="keyword">group</span> <span class="keyword">by</span> 分组字段列表</span><br><span class="line"></span><br><span class="line"><span class="keyword">having</span> 分组后条件列表</span><br><span class="line"></span><br><span class="line"><span class="keyword">order</span> <span class="keyword">by</span> 排序字段列表</span><br><span class="line"></span><br><span class="line">limit 分页参数;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="基本查询">基本查询</span></h3><h4><span id="查询多个字段">查询多个字段：</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段<span class="number">1</span>，字段<span class="number">2</span>，字段<span class="number">3.</span>..<span class="keyword">from</span> 表名;</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="设置别名">设置别名：</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段<span class="number">1</span>[<span class="keyword">as</span> 别名<span class="number">1</span>]，字段<span class="number">2</span> [<span class="keyword">as</span> 别名<span class="number">2</span>]......<span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="去除重复记录">去除重复记录：</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="keyword">distinct</span> 字段列表 <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="条件查询">条件查询</span></h3><p><strong>语法：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件列表;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>条件：</strong></p>
<table>
<thead>
<tr>
<th>比较运算符</th>
<th>功能</th>
</tr>
</thead>
<tbody><tr>
<td><strong>&gt;</strong></td>
<td></td>
</tr>
<tr>
<td><strong>&gt;&#x3D;</strong></td>
<td></td>
</tr>
<tr>
<td><strong>&lt;</strong></td>
<td></td>
</tr>
<tr>
<td><strong>&lt;&#x3D;</strong></td>
<td></td>
</tr>
<tr>
<td><strong>&#x3D;</strong></td>
<td></td>
</tr>
<tr>
<td><strong>&lt;&gt; 或 !&#x3D;</strong></td>
<td><strong>不等于</strong></td>
</tr>
<tr>
<td><strong>between…and…</strong></td>
<td><strong>在某个范围之内</strong></td>
</tr>
<tr>
<td><strong>in(…)</strong></td>
<td><strong>在in之后的括号中，多选一</strong></td>
</tr>
<tr>
<td><strong>like 占位符</strong></td>
<td><strong>模糊匹配（_匹配单个字符，%匹配任意个字符）</strong></td>
</tr>
<tr>
<td><strong>is null</strong></td>
<td></td>
</tr>
<tr>
<td><strong>and 或 &amp;&amp;</strong></td>
<td><strong>并且</strong></td>
</tr>
<tr>
<td><strong>or 或 ||</strong></td>
<td><strong>或</strong></td>
</tr>
<tr>
<td><strong>not 或 !</strong></td>
<td><strong>非</strong></td>
</tr>
</tbody></table>
<p><strong>eg：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br><span class="line">31</span><br><span class="line">32</span><br><span class="line">33</span><br><span class="line">34</span><br><span class="line">35</span><br><span class="line">36</span><br><span class="line">37</span><br><span class="line">38</span><br></pre></td><td class="code"><pre><span class="line">#二、条件查询</span><br><span class="line">#<span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表名 <span class="keyword">where</span> 条件列表;</span><br><span class="line">#<span class="number">1.</span>查询年龄等于<span class="number">25</span>的员工</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">=</span> <span class="number">25</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">2.</span>查询年龄小于<span class="number">20</span>的员工</span><br><span class="line"><span class="keyword">select</span>  <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">&lt;</span> <span class="number">20</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">3.</span>查询没有身份证信息的员工</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> idcard <span class="keyword">is</span> <span class="keyword">null</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">4.</span>查询有身份证信息的员工</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> idcard <span class="keyword">is</span> <span class="keyword">not null</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">5.</span>查询年龄不等于<span class="number">18</span>的员工</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">!=</span> <span class="number">18</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">&lt;&gt;</span> <span class="number">18</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">6.</span>查询年龄在<span class="number">20</span>岁到<span class="number">25</span>岁(包含<span class="number">25</span>)之间的员工信息</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">&gt;</span> <span class="number">20</span> <span class="operator">&amp;&amp;</span> age <span class="operator">&lt;=</span> <span class="number">25</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">&gt;</span> <span class="number">20</span> <span class="keyword">AND</span> age <span class="operator">&lt;=</span> <span class="number">25</span>;</span><br><span class="line"></span><br><span class="line">#两端都包含</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="keyword">between</span> <span class="number">15</span> <span class="keyword">and</span> <span class="number">25</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">7.</span>查询性别为女且年龄小于<span class="number">25</span>的员工信息</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> gender <span class="operator">=</span> <span class="string">&#x27;男&#x27;</span> <span class="operator">&amp;&amp;</span> age <span class="operator">&lt;</span> <span class="number">25</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">8.</span>查询年龄等于<span class="number">15</span>或者<span class="number">20</span>或者<span class="number">25</span>的员工</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">=</span> <span class="number">15</span> <span class="operator">||</span> age <span class="operator">=</span> <span class="number">20</span> <span class="operator">||</span> age <span class="operator">=</span> <span class="number">25</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">=</span> <span class="number">15</span> <span class="keyword">or</span> age <span class="operator">=</span> <span class="number">20</span> <span class="keyword">or</span> age <span class="operator">=</span> <span class="number">25</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="keyword">in</span>(<span class="number">15</span>, <span class="number">20</span>, <span class="number">25</span>);</span><br><span class="line"></span><br><span class="line">#<span class="number">9.</span>查询姓名为两个字的员工 模糊匹配</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> name <span class="keyword">like</span> <span class="string">&#x27;___&#x27;</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">10.</span>查询身份证号最后一位为X的员工</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> idcard <span class="keyword">like</span> <span class="string">&#x27;%X&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="聚合函数">聚合函数</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 聚合函数(字段列表) <span class="keyword">from</span> 表名;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>注意：</strong></p>
<blockquote>
<p>​    对一列进行计算 所有null值不参与聚合函数的计算</p>
</blockquote>
<table>
<thead>
<tr>
<th>函数</th>
<th>功能</th>
</tr>
</thead>
<tbody><tr>
<td><strong>count</strong></td>
<td><strong>统计数量</strong></td>
</tr>
<tr>
<td><strong>max</strong></td>
<td><strong>最大值</strong></td>
</tr>
<tr>
<td><strong>min</strong></td>
<td><strong>最小值</strong></td>
</tr>
<tr>
<td><strong>avg</strong></td>
<td><strong>平均值</strong></td>
</tr>
<tr>
<td><strong>sum</strong></td>
<td><strong>求和</strong></td>
</tr>
</tbody></table>
<p><strong>eg：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br></pre></td><td class="code"><pre><span class="line">#三、聚合函数</span><br><span class="line">#对一列进行计算 所有<span class="keyword">null</span>值不参与聚合函数的计算</span><br><span class="line">#<span class="keyword">select</span> 聚合函数(字段列表) <span class="keyword">from</span> 表名;</span><br><span class="line"></span><br><span class="line">#<span class="number">1.</span>统计数量</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">from</span> emp;</span><br><span class="line"></span><br><span class="line">#<span class="number">2.</span>统计企业员工的平均年龄</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">avg</span>(age) <span class="keyword">from</span> emp;</span><br><span class="line"></span><br><span class="line">#<span class="number">3.</span>最大年龄</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">max</span>(age) <span class="keyword">from</span> emp;</span><br><span class="line"></span><br><span class="line">#<span class="number">4.</span>最小年龄</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">min</span>(age) <span class="keyword">from</span> emp;</span><br><span class="line"></span><br><span class="line">#<span class="number">5.</span>统计北京地区员工年龄之和</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">sum</span>(age) <span class="keyword">from</span> emp <span class="keyword">where</span> workaddress <span class="operator">=</span> <span class="string">&#x27;北京&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="分组查询">分组查询</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表名 [<span class="keyword">where</span> 条件] <span class="keyword">group</span> <span class="keyword">by</span> 分组字段名 [<span class="keyword">having</span> 分组后过滤条件];</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<table>
<thead>
<tr>
<th><strong>where</strong></th>
<th><code>**分组之前执行,不满足where条件的不参与分组,where不能对聚合函数进行判断**</code></th>
</tr>
</thead>
<tbody><tr>
<td><strong>having</strong></td>
<td><code>**分组之后对结果进行过滤,having可以对聚合函数进行判断**</code></td>
</tr>
</tbody></table>
<p><strong>eg：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">#<span class="number">1.</span>根据性别分组， 统计男性与女性的数量</span><br><span class="line"><span class="keyword">select</span> gender, <span class="built_in">count</span>(<span class="operator">*</span>) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> gender;</span><br><span class="line"></span><br><span class="line">#<span class="number">2.</span>根据性别分组，统计男性和女性的平均年龄</span><br><span class="line"><span class="keyword">select</span>  gender, <span class="built_in">avg</span>(age) <span class="keyword">from</span> emp <span class="keyword">group</span> <span class="keyword">by</span> gender;</span><br><span class="line"></span><br><span class="line">#<span class="number">3.</span>查询年龄小于<span class="number">25</span>的员工, 并根据工作地址分组，获取员工数量大于等于<span class="number">3</span>的工作地址</span><br><span class="line"><span class="keyword">select</span> workaddress, <span class="built_in">count</span>(<span class="operator">*</span>) address_count <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">&lt;=</span> <span class="number">25</span> <span class="keyword">group</span> <span class="keyword">by</span> workaddress <span class="keyword">having</span> <span class="built_in">count</span>(<span class="operator">*</span>) <span class="operator">&gt;</span> <span class="number">1</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="排序查询">排序查询</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表名 <span class="keyword">order</span> <span class="keyword">by</span> 字段<span class="number">1</span> 排序方式<span class="number">1</span>, 字段<span class="number">2</span> 排序方式<span class="number">2</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<table>
<thead>
<tr>
<th><strong>asc</strong></th>
<th><strong>升序（默认）</strong></th>
</tr>
</thead>
<tbody><tr>
<td><strong>desc</strong></td>
<td><strong>降序</strong></td>
</tr>
</tbody></table>
<p><strong>eg：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line">#<span class="number">1.</span>根据年龄对公司员工进行升序排序</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> age <span class="keyword">asc</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">2.</span>根据年龄对公司员工进行降序排序</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> age <span class="keyword">desc</span></span><br><span class="line"></span><br><span class="line">#<span class="number">3.</span>根据年龄升序 根据id降序</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> age <span class="keyword">asc</span>, id <span class="keyword">desc</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="分页查询"><strong>分页查询</strong></span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表名 limit 起始索引, 查询记录数;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>eg：</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line">#<span class="number">1.</span>查询第<span class="number">1</span>页员工数据，每页展示<span class="number">2</span>条记录</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp limit <span class="number">0</span>, <span class="number">2</span>;</span><br><span class="line"></span><br><span class="line">#<span class="number">2.</span>查询第<span class="number">2</span>页员工数据，每页展示<span class="number">10</span>条数据 <span class="comment">------&gt;(页码 - 1)*页展示记录数</span></span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp limit <span class="number">2</span>, <span class="number">2</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<hr>
<h2><span id="4dcl数据控制语言">4.DCL(数据控制语言)</span></h2><h3><span id="管理用户">管理用户</span></h3><p><strong>注意：</strong></p>
<blockquote>
<p>​    主机名可以使用%通配</p>
</blockquote>
<h4><span id="查询用户">查询用户：</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">use mysql;</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> <span class="keyword">user</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="创建用户">创建用户：</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create</span> <span class="keyword">user</span> <span class="string">&#x27;用户名&#x27;</span>@‘主机名’ identified <span class="keyword">by</span> <span class="string">&#x27;密码&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="修改用户密码"><strong>修改用户密码：</strong></span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">alter</span> <span class="keyword">user</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span> identified <span class="keyword">with</span> mysql_native_password <span class="keyword">by</span> <span class="string">&#x27;新密码&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="删除用户">删除用户：</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">drop</span> <span class="keyword">user</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="权限控制">权限控制</span></h3><table>
<thead>
<tr>
<th>权限</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td>all, all privileges</td>
<td>所有权限</td>
</tr>
<tr>
<td>select</td>
<td>查询数据</td>
</tr>
<tr>
<td>insert</td>
<td>插入数据</td>
</tr>
<tr>
<td>update</td>
<td>修改数据</td>
</tr>
<tr>
<td>delete</td>
<td>删除数据</td>
</tr>
<tr>
<td>alter</td>
<td>修改表</td>
</tr>
<tr>
<td>drop</td>
<td>删除数据库&#x2F;表&#x2F;视图</td>
</tr>
<tr>
<td>create</td>
<td>创建数据库&#x2F;表</td>
</tr>
</tbody></table>
<h4><span id="查询权限">查询权限:</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">show</span> grants <span class="keyword">for</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="授予权限">授予权限:</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">grant</span> 权限列表 <span class="keyword">on</span> 数据库名.表名 <span class="keyword">to</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>注意：</strong></p>
<blockquote>
<p>​    授权时数据库名和表名可以用*进行通配，代表所有</p>
</blockquote>
<h4><span id="撤销权限">撤销权限:</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">revoke</span> 权限列表 <span class="keyword">on</span> 数据库名.表名 <span class="keyword">from</span> <span class="string">&#x27;用户名&#x27;</span>@<span class="string">&#x27;主机名&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<hr>
<hr>
<h1><span id="函数">函数</span></h1><h2><span id="查看函数返回结果">查看函数返回结果</span></h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">+</span> 函数;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h2><span id="字符串函数">字符串函数</span></h2><table>
<thead>
<tr>
<th>函数</th>
<th>功能</th>
</tr>
</thead>
<tbody><tr>
<td><code>concat(s1, s2, s3...sn)</code></td>
<td><code>拼接</code></td>
</tr>
<tr>
<td><code>lower(s)</code></td>
<td><code>转小写</code></td>
</tr>
<tr>
<td><code>upper(s)</code></td>
<td><code>转大写</code></td>
</tr>
<tr>
<td><code>lpad(s, n, pad)</code></td>
<td><code>左填充，用pad对s进行左填充以达到n个字符</code></td>
</tr>
<tr>
<td><code>rpad(s, n, pad)</code></td>
<td><code>右填充，用pad对s进行右填充以达到n个字符</code></td>
</tr>
<tr>
<td><code>trim(s) </code></td>
<td><code>去掉s头部和尾部的空格</code></td>
</tr>
<tr>
<td><code>substring(s, st, len)</code></td>
<td><code>截取从st开始的len个字符 st从1开始</code></td>
</tr>
</tbody></table>
<p>eg：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br></pre></td><td class="code"><pre><span class="line">#concat</span><br><span class="line"><span class="keyword">select</span> concat(<span class="string">&#x27;Karry&#x27;</span>,<span class="string">&#x27; Wang&#x27;</span>);</span><br><span class="line"></span><br><span class="line">#lower</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">lower</span>(<span class="string">&#x27;HELLO&#x27;</span>);</span><br><span class="line"></span><br><span class="line">#upper</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">upper</span>(<span class="string">&#x27;hello&#x27;</span>);</span><br><span class="line"></span><br><span class="line">#lpad</span><br><span class="line"><span class="keyword">select</span> lpad(<span class="string">&#x27;01&#x27;</span>, <span class="number">9</span>, <span class="string">&#x27;-&#x27;</span>);</span><br><span class="line"></span><br><span class="line">#rpad</span><br><span class="line"><span class="keyword">select</span> rpad(<span class="string">&#x27;01&#x27;</span>, <span class="number">9</span>, <span class="string">&#x27;-&#x27;</span>);</span><br><span class="line"></span><br><span class="line">#trim</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">trim</span>(<span class="string">&#x27;  010  101   &#x27;</span>);</span><br><span class="line"></span><br><span class="line">#substring</span><br><span class="line"><span class="keyword">select</span> <span class="built_in">substring</span>(<span class="string">&#x27;Hello World&#x27;</span>, <span class="number">1</span>, <span class="number">5</span>);</span><br><span class="line"></span><br><span class="line">#<span class="number">1</span></span><br><span class="line"><span class="keyword">update</span> emp <span class="keyword">set</span> working<span class="operator">=</span>lpad(working, <span class="number">5</span>, <span class="string">&#x27;0&#x27;</span>);</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h2><span id="数值函数">数值函数</span></h2><table>
<thead>
<tr>
<th><strong>函数</strong></th>
<th><strong>功能</strong></th>
</tr>
</thead>
<tbody><tr>
<td><code>ceil(x)</code></td>
<td><code>向上取整</code></td>
</tr>
<tr>
<td><code>floor(x)</code></td>
<td><code>向下取整</code></td>
</tr>
<tr>
<td><code>mod(x, y)</code></td>
<td><code>返回x % y</code></td>
</tr>
<tr>
<td><code>rand()</code></td>
<td><code>返回0~1内的随机数</code></td>
</tr>
<tr>
<td><code>round(x, y)</code></td>
<td><code>求x四舍五入的值，保留y位小数</code></td>
</tr>
</tbody></table>
<p>eg： </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#生成一个六位验证码</span><br><span class="line"><span class="keyword">select</span> rpad(round(rand()<span class="operator">*</span><span class="number">1000000</span>, <span class="number">0</span>), <span class="number">6</span>, round(rand()<span class="operator">*</span><span class="number">10</span>, <span class="number">0</span>));</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h2><span id="日期函数">日期函数</span></h2><table>
<thead>
<tr>
<th><strong>函数</strong></th>
<th><strong>功能</strong></th>
</tr>
</thead>
<tbody><tr>
<td><code>curdate()</code></td>
<td><code>返回当前日期</code></td>
</tr>
<tr>
<td><code>curtime()</code></td>
<td><code>返回当前时间</code></td>
</tr>
<tr>
<td><code>now()</code></td>
<td><code>返回当前日期和时间</code></td>
</tr>
<tr>
<td><code>year(date)</code></td>
<td><code>获取date的年份</code></td>
</tr>
<tr>
<td><code>month(date)</code></td>
<td><code>获取date的月份</code></td>
</tr>
<tr>
<td><code>day(date)</code></td>
<td><code>获取date的日期</code></td>
</tr>
<tr>
<td><code>date_add(date, interval expr type)</code></td>
<td><code>返回一个日期加上一个时间间隔expr后的时间值</code></td>
</tr>
<tr>
<td><code>datediff(date1, date2)</code></td>
<td><code>返回起始时间date1和结束时间date2之间的天数(date1 - date2)</code></td>
</tr>
</tbody></table>
<p>eg: </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> date_add(now(), <span class="type">interval</span> <span class="number">70</span> <span class="keyword">day</span>);</span><br><span class="line"><span class="keyword">select</span> date_add(now(), <span class="type">interval</span> <span class="number">70</span> <span class="keyword">month</span>);</span><br><span class="line"></span><br><span class="line"><span class="keyword">select</span> datediff(now(), <span class="string">&#x27;2022-9-21&#x27;</span>);</span><br><span class="line"></span><br><span class="line">#根据入职时长倒序排序</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp;</span><br><span class="line"><span class="keyword">select</span> name, datediff(now(), entrydate) <span class="keyword">from</span> emp <span class="keyword">order</span> <span class="keyword">by</span> datediff(now(), entrydate) <span class="keyword">desc</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h2><span id="流程函数">流程函数</span></h2><table>
<thead>
<tr>
<th><strong>函数</strong></th>
<th><strong>功能</strong></th>
</tr>
</thead>
<tbody><tr>
<td><code>if(value, t, f)</code></td>
<td><code>如果val为true，返回t，否则返回f</code></td>
</tr>
<tr>
<td><code>ifnull(value1, value2)</code></td>
<td><code>如果val1不为空，返回val1，否则返回val2</code></td>
</tr>
<tr>
<td><code>case when [val1] then [res1]... else [default] end</code></td>
<td><code>如果val1为true，返回res1，否则返回default默认值</code></td>
</tr>
<tr>
<td><code>case [expr] when [val1] then [res1]...else [default] end</code></td>
<td><code>如果expr=val1，返回res1，否则返回default默认值</code></td>
</tr>
</tbody></table>
<p>eg: </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> if(<span class="literal">true</span>, <span class="string">&#x27;ok&#x27;</span>, <span class="string">&#x27;no&#x27;</span>);</span><br><span class="line"><span class="keyword">select</span>  ifnull(<span class="string">&#x27;1&#x27;</span>, <span class="string">&#x27;2&#x27;</span>);</span><br><span class="line"><span class="keyword">select</span> ifnull(<span class="keyword">null</span>, <span class="string">&#x27;default&#x27;</span>);</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">case</span> <span class="keyword">when</span> <span class="literal">true</span> <span class="keyword">then</span> <span class="string">&#x27;ok&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;no&#x27;</span> <span class="keyword">end</span>;</span><br><span class="line"><span class="keyword">select</span> <span class="keyword">case</span> <span class="string">&#x27;2&#x27;</span> <span class="keyword">when</span> <span class="string">&#x27;2&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;yes&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;no&#x27;</span> <span class="keyword">end</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>eg:</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> name,</span><br><span class="line">       workaddress,</span><br><span class="line">       <span class="keyword">case</span> workaddress <span class="keyword">when</span> <span class="string">&#x27;南京&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;一线城市&#x27;</span></span><br><span class="line">                        <span class="keyword">when</span> <span class="string">&#x27;重庆&#x27;</span> <span class="keyword">then</span> <span class="string">&#x27;一线城市&#x27;</span></span><br><span class="line">                        <span class="keyword">else</span> <span class="string">&#x27;二线城市&#x27;</span> <span class="keyword">end</span></span><br><span class="line">       <span class="keyword">as</span> citydiffer</span><br><span class="line"><span class="keyword">from</span> emp;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>eg:</strong> </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span></span><br><span class="line">    id,</span><br><span class="line">    name,</span><br><span class="line">    (<span class="keyword">case</span> <span class="keyword">when</span> math <span class="operator">&gt;=</span> <span class="number">85</span> <span class="keyword">then</span> <span class="string">&#x27;优秀&#x27;</span> <span class="keyword">when</span> math <span class="operator">&gt;=</span> <span class="number">60</span> <span class="keyword">then</span> <span class="string">&#x27;及格&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;不及格&#x27;</span> <span class="keyword">end</span>) <span class="keyword">as</span> <span class="string">&#x27;数学&#x27;</span>,</span><br><span class="line">    (<span class="keyword">case</span> <span class="keyword">when</span> English <span class="operator">&gt;=</span> <span class="number">85</span> <span class="keyword">then</span> <span class="string">&#x27;优秀&#x27;</span> <span class="keyword">when</span> English <span class="operator">&gt;=</span> <span class="number">60</span> <span class="keyword">then</span> <span class="string">&#x27;及格&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;不及格&#x27;</span> <span class="keyword">end</span>) <span class="keyword">as</span> <span class="string">&#x27;英语&#x27;</span>,</span><br><span class="line">    (<span class="keyword">case</span> <span class="keyword">when</span> Chinese <span class="operator">&gt;=</span> <span class="number">85</span> <span class="keyword">then</span> <span class="string">&#x27;优秀&#x27;</span> <span class="keyword">when</span> Chinese <span class="operator">&gt;=</span> <span class="number">60</span> <span class="keyword">then</span> <span class="string">&#x27;及格&#x27;</span> <span class="keyword">else</span> <span class="string">&#x27;不及格&#x27;</span> <span class="keyword">end</span>) <span class="keyword">as</span> <span class="string">&#x27;语文&#x27;</span></span><br><span class="line"><span class="keyword">from</span> score;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<hr>
<h1><span id="约束">约束</span></h1><h2><span id="概述">概述</span></h2><h3><span id="概念">概念：</span></h3><blockquote>
<p>​        约束是作用于表中字段上的规则，用于限制存储在表中的数据</p>
<p>​        约束可以在创建表&#x2F;修改表的时候添加</p>
</blockquote>
<h3><span id="目的">目的：</span></h3><blockquote>
<p>​        保证数据库中数据的正确性、有效性和完整性</p>
</blockquote>
<h3><span id="分类">分类：</span></h3><table>
<thead>
<tr>
<th>约束</th>
<th>描述</th>
<th>关键字</th>
</tr>
</thead>
<tbody><tr>
<td>非空约束</td>
<td>限制该字段的数据不能为null</td>
<td>not null</td>
</tr>
<tr>
<td>唯一约束</td>
<td>保证该字段的所有数据都是唯一的、不重复的</td>
<td>unique</td>
</tr>
<tr>
<td>主键约束</td>
<td>主键是一行数据的唯一标识，要求非空且唯一</td>
<td>primary key (自增:auto_increment)</td>
</tr>
<tr>
<td>默认约束</td>
<td>保存数据时，如果未指定该字段的值，则采用默认值</td>
<td>default</td>
</tr>
<tr>
<td>检查约束</td>
<td>保证字段值满足某一条件</td>
<td>check</td>
</tr>
<tr>
<td>外键约束</td>
<td>用来让两张表的数据之间建立连接，保证数据的一致性和完整性</td>
<td>foreign key</td>
</tr>
</tbody></table>
<h2><span id="约束演示">约束演示</span></h2><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create table</span> <span class="keyword">user</span>(</span><br><span class="line">    id <span class="type">int</span> <span class="keyword">primary key</span> auto_increment comment <span class="string">&#x27;主键&#x27;</span>,</span><br><span class="line">    name <span class="type">varchar</span>(<span class="number">10</span>) <span class="keyword">not null</span> <span class="keyword">unique</span> comment <span class="string">&#x27;姓名&#x27;</span>,</span><br><span class="line">    age <span class="type">int</span> <span class="keyword">check</span> ( age <span class="operator">&gt;</span> <span class="number">0</span> <span class="operator">&amp;&amp;</span> age <span class="operator">&lt;=</span> <span class="number">120</span> ) comment <span class="string">&#x27;年龄&#x27;</span>,</span><br><span class="line">    status <span class="type">char</span>(<span class="number">1</span>) <span class="keyword">default</span> <span class="string">&#x27;1&#x27;</span> comment <span class="string">&#x27;状态&#x27;</span>,</span><br><span class="line">    gender <span class="type">char</span>(<span class="number">1</span>) comment <span class="string">&#x27;性别&#x27;</span></span><br><span class="line">) comment <span class="string">&#x27;用户表&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h2><span id="外键约束">外键约束</span></h2><h3><span id="概念">概念</span></h3><blockquote>
<p>​    <strong>子表（从表）</strong>：具有外键的表</p>
<p>​    <strong>父表（主表）</strong>：外键所关联的表</p>
</blockquote>
<h3><span id="语法">语法</span></h3><h4><span id="添加外键">添加外键</span></h4><p><strong>sql语句添加：</strong></p>
<blockquote>
<p><strong>alter table (从表) add constraint 外键名称 foreign key (外键字段名) references 主表(主键);</strong></p>
</blockquote>
<p><strong>创建表时添加：</strong> </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">create table</span> emp1(</span><br><span class="line">    id <span class="type">int</span> <span class="keyword">primary key</span> auto_increment comment <span class="string">&#x27;主键&#x27;</span>,</span><br><span class="line">    name <span class="type">varchar</span>(<span class="number">10</span>) <span class="keyword">not null</span> <span class="keyword">unique</span> comment <span class="string">&#x27;姓名&#x27;</span>,</span><br><span class="line">    age <span class="type">int</span> <span class="keyword">check</span> ( age <span class="operator">&gt;</span> <span class="number">0</span> <span class="operator">&amp;&amp;</span> age <span class="operator">&lt;=</span> <span class="number">120</span> ) comment <span class="string">&#x27;年龄&#x27;</span>,</span><br><span class="line">    job <span class="type">varchar</span>(<span class="number">20</span>) comment <span class="string">&#x27;职位&#x27;</span>,</span><br><span class="line">    salary <span class="type">int</span> comment <span class="string">&#x27;薪资&#x27;</span>,</span><br><span class="line">    entrydate <span class="type">date</span> comment <span class="string">&#x27;入职时间&#x27;</span>,</span><br><span class="line">    managerid <span class="type">int</span> comment <span class="string">&#x27;直属领导id&#x27;</span>,</span><br><span class="line">    dept_id <span class="type">int</span> comment <span class="string">&#x27;部门id&#x27;</span>,</span><br><span class="line">    <span class="keyword">constraint</span> dept_id_fk <span class="keyword">foreign key</span> (dept_id) <span class="keyword">references</span> dept(id)</span><br><span class="line">)comment <span class="string">&#x27;员工表&#x27;</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p>eg：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#添加外键</span><br><span class="line"><span class="keyword">alter table</span> emp <span class="keyword">add constraint</span> fk_emp_dept_id <span class="keyword">foreign key</span> (dept_id) <span class="keyword">references</span> dept(id);</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="删除外键">删除外键</span></h4><blockquote>
<p><strong>alter table 从表 drop foreign key 外键名称；</strong></p>
</blockquote>
<p> eg：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#删除外键</span><br><span class="line"><span class="keyword">alter table</span> emp <span class="keyword">drop</span> <span class="keyword">foreign key</span> fk_emp_dept_id;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="指定外键删除x2f更新行为">指定外键删除&#x2F;更新行为</span></h4><table>
<thead>
<tr>
<th>行为</th>
<th>说明</th>
</tr>
</thead>
<tbody><tr>
<td><strong>not action &#x2F;</strong> 			<strong>restrict</strong></td>
<td>默认行为，当父表删除或更新记录时，如果当前记录对应的父表与子表有关联，则不予删除或更新</td>
</tr>
<tr>
<td><strong>cascade</strong></td>
<td>当父表删除或更新记录时，如果有外键关联父表与子表，则也删除或者更新外键在子表中的记录</td>
</tr>
<tr>
<td><strong>set null</strong></td>
<td>当父表删除或更新记录时，如果有外键关联父表与子表，则设置子表中该外键为null</td>
</tr>
<tr>
<td><strong>set default</strong></td>
<td>当父表删除或更新记录时，如果有外键关联父表与子表，则设置子表中该外键为默认值（innodb不支持）</td>
</tr>
</tbody></table>
<blockquote>
<p><strong>alter table (从表) add constraint 外键名称 foreign key (外键字段名) references 主表(主键) on update 行为 on delete 行为;</strong></p>
</blockquote>
<p> eg：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#指定外键更新或删除行为</span><br><span class="line"><span class="keyword">alter table</span> emp <span class="keyword">add constraint</span> fk_emp_dept_id <span class="keyword">foreign key</span> (dept_id) <span class="keyword">references</span> dept(id) <span class="keyword">on</span> <span class="keyword">update</span> cascade <span class="keyword">on</span> <span class="keyword">DELETE</span> cascade;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="example">example：</span></h4><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br><span class="line">5</span><br><span class="line">6</span><br><span class="line">7</span><br><span class="line">8</span><br><span class="line">9</span><br><span class="line">10</span><br><span class="line">11</span><br><span class="line">12</span><br><span class="line">13</span><br><span class="line">14</span><br><span class="line">15</span><br><span class="line">16</span><br><span class="line">17</span><br><span class="line">18</span><br><span class="line">19</span><br><span class="line">20</span><br><span class="line">21</span><br><span class="line">22</span><br><span class="line">23</span><br><span class="line">24</span><br><span class="line">25</span><br><span class="line">26</span><br><span class="line">27</span><br><span class="line">28</span><br><span class="line">29</span><br><span class="line">30</span><br></pre></td><td class="code"><pre><span class="line">#主表</span><br><span class="line"><span class="keyword">create table</span> dept(</span><br><span class="line">    id <span class="type">int</span> auto_increment <span class="keyword">primary key</span> comment <span class="string">&#x27;id&#x27;</span>,</span><br><span class="line">    name <span class="type">varchar</span>(<span class="number">50</span>) <span class="keyword">not null</span> comment <span class="string">&#x27;部门名称&#x27;</span></span><br><span class="line">)comment <span class="string">&#x27;部门表&#x27;</span>;</span><br><span class="line"></span><br><span class="line">#从表</span><br><span class="line"><span class="keyword">create table</span> emp(</span><br><span class="line">    id <span class="type">int</span> <span class="keyword">primary key</span> auto_increment comment <span class="string">&#x27;主键&#x27;</span>,</span><br><span class="line">    name <span class="type">varchar</span>(<span class="number">10</span>) <span class="keyword">not null</span> <span class="keyword">unique</span> comment <span class="string">&#x27;姓名&#x27;</span>,</span><br><span class="line">    age <span class="type">int</span> <span class="keyword">check</span> ( age <span class="operator">&gt;</span> <span class="number">0</span> <span class="operator">&amp;&amp;</span> age <span class="operator">&lt;=</span> <span class="number">120</span> ) comment <span class="string">&#x27;年龄&#x27;</span>,</span><br><span class="line">    job <span class="type">varchar</span>(<span class="number">20</span>) comment <span class="string">&#x27;职位&#x27;</span>,</span><br><span class="line">    salary <span class="type">int</span> comment <span class="string">&#x27;薪资&#x27;</span>,</span><br><span class="line">    entrydate <span class="type">date</span> comment <span class="string">&#x27;入职时间&#x27;</span>,</span><br><span class="line">    managerid <span class="type">int</span> comment <span class="string">&#x27;直属领导id&#x27;</span>,</span><br><span class="line">    dept_id <span class="type">int</span> comment <span class="string">&#x27;部门id&#x27;</span></span><br><span class="line">)comment <span class="string">&#x27;员工表&#x27;</span>;</span><br><span class="line"></span><br><span class="line">#插入数据</span><br><span class="line"><span class="keyword">insert into</span> dept(name) <span class="keyword">values</span> (<span class="string">&#x27;研发部&#x27;</span>), (<span class="string">&#x27;市场部&#x27;</span>), (<span class="string">&#x27;销售部&#x27;</span>), (<span class="string">&#x27;外交部&#x27;</span>);</span><br><span class="line"><span class="keyword">insert into</span> emp(id, name, age, job, salary, entrydate, managerid, dept_id) <span class="keyword">values</span></span><br><span class="line">                                                                               (<span class="number">1</span>, <span class="string">&#x27;成朗&#x27;</span>, <span class="number">24</span>, <span class="string">&#x27;外交官&#x27;</span>, <span class="number">5000</span>, <span class="string">&#x27;1-1-1&#x27;</span>, <span class="number">1</span>, <span class="number">4</span>),</span><br><span class="line">                                                                               (<span class="number">2</span>, <span class="string">&#x27;庄文杰&#x27;</span>, <span class="number">18</span>, <span class="string">&#x27;学生&#x27;</span>, <span class="number">3000</span>, <span class="string">&#x27;1-1-1&#x27;</span>, <span class="number">2</span>, <span class="number">2</span>);</span><br><span class="line">#外键约束</span><br><span class="line">#添加外键</span><br><span class="line"><span class="keyword">alter table</span> emp <span class="keyword">add constraint</span> fk_emp_dept_id <span class="keyword">foreign key</span> (dept_id) <span class="keyword">references</span> dept(id);</span><br><span class="line">#删除外键</span><br><span class="line"><span class="keyword">alter table</span> emp <span class="keyword">drop</span> <span class="keyword">foreign key</span> fk_emp_dept_id;</span><br><span class="line">#指定外键更新或删除行为</span><br><span class="line"><span class="keyword">alter table</span> emp <span class="keyword">add constraint</span> fk_emp_dept_id <span class="keyword">foreign key</span> (dept_id) <span class="keyword">references</span> dept(id) <span class="keyword">on</span> <span class="keyword">update</span> cascade <span class="keyword">on</span> <span class="keyword">DELETE</span> cascade;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<hr>
<h1><span id="多表查询">多表查询</span></h1><h2><span id="多表关系">多表关系</span></h2><h3><span id="一对多多对一">一对多（多对一）</span></h3><blockquote>
<p>在多的一方建立外键，指向一的一方的主键</p>
</blockquote>
<h3><span id="多对多中间表">多对多（中间表）</span></h3><blockquote>
<p>建立第三张中间表，中间表至少包含两个外键，分别关联多方主键</p>
</blockquote>
<h3><span id="一对一单表拆分">一对一（单表拆分）</span></h3><blockquote>
<p>在任意一方加入外键，关联另外一方的主键，并且设置外键为唯一的（unique）</p>
</blockquote>
<h2><span id="多表查询概述">多表查询概述</span></h2><h3><span id="笛卡尔积">笛卡尔积</span></h3><p>两个集合所有组合的情况</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#多表查询</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp, dept;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="消除无效笛卡尔积">消除无效笛卡尔积</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#多表查询</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp, dept <span class="keyword">where</span> emp.dept_id <span class="operator">=</span> dept.id;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h2><span id="多表查询分类">多表查询分类</span></h2><h3><span id="连接查询">连接查询</span></h3><h4><span id="外连接">外连接</span></h4><p><strong>左外连接：</strong></p>
<p><img src="/./../images/05a0f6d0b47e762207d779244391fbab.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<blockquote>
<p>​    查询左表所有数据，以及两张表交集部分数据</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">left</span> [<span class="keyword">outer</span>] <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 条件...;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>右外连接：</strong></p>
<p><img src="/./../images/546c8893507a607443d49565ec7f1701.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<blockquote>
<p>​    查询右表所有数据，以及两张表交集部分数据</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">right</span> [<span class="keyword">outer</span>] <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 条件...;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p> eg:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#左外连接</span><br><span class="line"><span class="keyword">select</span> e.<span class="operator">*</span>, d.name <span class="keyword">from</span> emp e <span class="keyword">left</span> <span class="keyword">outer</span> <span class="keyword">join</span> dept d <span class="keyword">on</span> e.dept_id <span class="operator">=</span> d.id;</span><br><span class="line">#右外连接</span><br><span class="line"><span class="keyword">select</span> d.<span class="operator">*</span>, e.<span class="operator">*</span> <span class="keyword">from</span> emp e <span class="keyword">right</span> <span class="keyword">outer</span> <span class="keyword">join</span> dept d <span class="keyword">on</span> e.dept_id <span class="operator">=</span> d.id;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="内连接">内连接</span></h4><p><img src="/./../images/8e97fbe608e51b4aca43e395febc36e6.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<blockquote>
<p>​    相当于查询两张表交集部分数据</p>
</blockquote>
<h5><span id="隐式内连接">隐式内连接</span></h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span>, 表<span class="number">2</span> <span class="keyword">where</span> 条件...;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h5><span id="显式内连接">显式内连接</span></h5><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> [<span class="keyword">inner</span>] <span class="keyword">join</span> 表<span class="number">2</span> <span class="keyword">on</span> 连接条件...;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p>eg:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br><span class="line">4</span><br></pre></td><td class="code"><pre><span class="line">#显式内连接</span><br><span class="line"><span class="keyword">select</span> emp.name, dept.name <span class="keyword">from</span> emp, dept <span class="keyword">where</span> emp.dept_id <span class="operator">=</span> dept.id;</span><br><span class="line">#隐式内连接</span><br><span class="line"><span class="keyword">select</span> emp.name, dept.name <span class="keyword">from</span> emp <span class="keyword">inner</span> <span class="keyword">join</span> dept <span class="keyword">where</span> emp.dept_id <span class="operator">=</span> dept.id;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h4><span id="自连接">自连接</span></h4><blockquote>
<p>​    当前表与自身的连接查询，自连接必须使用表别名</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> 别名<span class="number">1</span> <span class="keyword">join</span> 表<span class="number">1</span> 别名<span class="number">2</span> <span class="keyword">on</span> 条件...;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p>eg:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">#自连接</span><br><span class="line"><span class="keyword">select</span> e1.name, e2.name <span class="keyword">from</span> emp e1 <span class="keyword">join</span> emp e2 <span class="keyword">on</span> e1.managerid <span class="operator">=</span> e2.managerid;</span><br><span class="line"><span class="keyword">select</span> e1.<span class="operator">*</span>, e2.name <span class="keyword">from</span> emp e1 <span class="keyword">left</span> <span class="keyword">outer</span> <span class="keyword">join</span> emp e2 <span class="keyword">on</span> e1.managerid <span class="operator">=</span> e2.managerid;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="联合查询">联合查询</span></h3><p><strong>union查询</strong></p>
<blockquote>
<p>把多次查询的结果合并起来，形成一个新的查询结果集</p>
<p><strong>union:</strong></p>
<p>​        对查询结果进行去重操作</p>
<p><strong>union all:</strong></p>
<p>​        将查询结果直接输出</p>
<p><strong>注意：</strong></p>
<p>​        联合查询的多张表字段列表个数及类型必须保持一致，查询结果的字段名称为第一次查询字段名</p>
</blockquote>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">1</span> ... <span class="keyword">union</span> [<span class="keyword">all</span>] <span class="keyword">select</span> 字段列表 <span class="keyword">from</span> 表<span class="number">2</span> ...;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p>eg:</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">#联合查询</span><br><span class="line"><span class="keyword">select</span> emp.name, emp.age, emp.salary <span class="keyword">from</span> emp <span class="keyword">where</span> salary <span class="operator">&lt;=</span> <span class="number">5000</span> <span class="keyword">union</span></span><br><span class="line">                                                          <span class="keyword">select</span> emp.name, emp.age, emp.salary <span class="keyword">from</span> emp <span class="keyword">where</span> age <span class="operator">&gt;=</span> <span class="number">10</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="子查询嵌套查询">子查询（嵌套查询）</span></h3><h5><span id="概念">概念</span></h5><blockquote>
<p>​    SQL语句中嵌套select语句，成为嵌套查询（子查询）</p>
</blockquote>
<h5><span id="语法">语法</span></h5><p><img src="/./../images/d8ae77fa79ac248f0315035160324ffb.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">insert</span><span class="operator">/</span><span class="keyword">update</span><span class="operator">/</span><span class="keyword">delete</span><span class="operator">/</span><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> 表<span class="number">1</span> <span class="keyword">where</span> column1 <span class="operator">=</span> (<span class="keyword">select</span> column1 <span class="keyword">from</span> 表<span class="number">2</span>);</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h5><span id="分类">分类</span></h5><p>按子查询结果：</p>
<blockquote>
<p>标量子查询，列子查询， 行子查询，表子查询 </p>
</blockquote>
<p>按子查询位置</p>
<blockquote>
<p>where之后、from之后、select之后</p>
</blockquote>
<h5><span id="标量子查询单个值">标量子查询（单个值）</span></h5><blockquote>
<p>​    子查询返回结果为单个值（数字，字符串，日期……）</p>
<p>​    <strong>常用操作符</strong>：&#x3D; 等于</p>
<p>​               &lt;&gt; 不等于</p>
<p>​                &gt; 大于</p>
<p>​               &gt;&#x3D; 大于等于</p>
<p>​                &lt; 小于</p>
<p>​               &lt;&#x3D; 小于等于</p>
</blockquote>
<p>eg： </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="operator">=</span> (<span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name <span class="operator">=</span> <span class="string">&#x27;外交部&#x27;</span>);</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> entrydate <span class="operator">&gt;=</span> (<span class="keyword">select</span> entrydate <span class="keyword">from</span> emp <span class="keyword">where</span> name <span class="operator">=</span> <span class="string">&#x27;成朗&#x27;</span>);</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h5><span id="列子查询一列">列子查询（一列）</span></h5><blockquote>
<p>​    子查询返回结果为一列（可以是多行）</p>
<p>​    <strong>常见操作符：</strong></p>
<p>​                in     在指定的集合范围之内，多选一</p>
<p>​                not in  不在指定集合范围之内</p>
<p>​                any    子查询返回列表中，有任何一个满足即可</p>
<p>​                some  与any相同 </p>
<p>​                all     子查询返回列表的所有值都必须满足</p>
</blockquote>
<p> eg：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="keyword">in</span> (<span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name <span class="operator">=</span> <span class="string">&#x27;外交部&#x27;</span> <span class="keyword">or</span> name <span class="operator">=</span> <span class="string">&#x27;销售部&#x27;</span>);</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> salary <span class="operator">&gt;</span> <span class="keyword">all</span>(<span class="keyword">select</span> salary <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="operator">=</span> (<span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name <span class="operator">=</span> <span class="string">&#x27;外交部&#x27;</span>));</span><br><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> salary <span class="operator">&gt;</span> <span class="keyword">some</span>(<span class="keyword">select</span> salary <span class="keyword">from</span> emp <span class="keyword">where</span> dept_id <span class="operator">=</span> (<span class="keyword">select</span> id <span class="keyword">from</span> dept <span class="keyword">where</span> name <span class="operator">=</span> <span class="string">&#x27;销售部&#x27;</span>));</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h5><span id="行子查询一行">行子查询（一行）</span></h5><blockquote>
<p>​    子查询返回结果为一行</p>
<p>​    <strong>常见操作符：</strong></p>
<p>​                &#x3D;        等于</p>
<p>​                &lt;&gt;       不等于</p>
<p>​                in       在集合范围之内 </p>
<p>​                not in    不在集合范围之内</p>
</blockquote>
<p>eg： </p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> (salary, managerid) <span class="operator">=</span> (<span class="keyword">select</span> salary, emp.managerid <span class="keyword">from</span> emp <span class="keyword">where</span> name <span class="operator">=</span> <span class="string">&#x27;成朗&#x27;</span>);</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h5><span id="表子查询多行多列">表子查询（多行多列）</span></h5><blockquote>
<p>​    子查询返回结果为多行多列</p>
<p>​    <strong>常见操作符：</strong></p>
<p>​                in 在集合范围之内</p>
<p>​        </p>
</blockquote>
<p> eg：</p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">select</span>  <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> (salary, dept_id) <span class="keyword">in</span> (<span class="keyword">select</span> salary, dept_id <span class="keyword">from</span> emp <span class="keyword">where</span> name <span class="operator">=</span> <span class="string">&#x27;成朗&#x27;</span> <span class="keyword">or</span> name <span class="operator">=</span> <span class="string">&#x27;庄文杰&#x27;</span>);</span><br><span class="line"><span class="keyword">select</span> e.<span class="operator">*</span>, dept.name <span class="keyword">from</span> (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> entrydate <span class="operator">&gt;=</span> <span class="string">&#x27;1-1-1&#x27;</span>) e, dept <span class="keyword">where</span> e.dept_id <span class="operator">=</span> dept.id;</span><br><span class="line"><span class="keyword">select</span> e.<span class="operator">*</span>, d.<span class="operator">*</span> <span class="keyword">from</span> (<span class="keyword">select</span> <span class="operator">*</span> <span class="keyword">from</span> emp <span class="keyword">where</span> entrydate <span class="operator">&gt;=</span> <span class="string">&#x27;1-1-1&#x27;</span>) e <span class="keyword">left</span> <span class="keyword">join</span> dept d <span class="keyword">on</span> e.dept_id <span class="operator">=</span> d.id;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<hr>
<h1><span id="事务">事务</span></h1><h2><span id="简介来自百度百科">简介（来自百度百科）</span></h2><blockquote>
<p><strong>概念：</strong>数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，<strong>是一个不可分割的工作单位</strong>。事务由事务开始与事务结束之间执行的全部数据库操作组成</p>
<p><strong>注意：默认MySQL的事务是自动提交的</strong>，也就是说，当执行一条DML语句，MySQL会立即隐式的提交事务</p>
</blockquote>
<h2><span id="操作控制事务">操作（控制事务）</span></h2><h3><span id="查看x2f设置事务提交方式">查看&#x2F;设置事务提交方式</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line">#为<span class="number">1</span>是自动提交 为<span class="number">0</span>是手动提交</span><br><span class="line"><span class="keyword">select</span> @<span class="variable">@autocommit</span>; #查看事务提交方式</span><br><span class="line"><span class="keyword">set</span> @<span class="variable">@autocommit</span> <span class="operator">=</span> <span class="number">0</span>; #设置事务提交方式</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><img src="/./../images/a8d9af9f10dc60866ca3584575535c59.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<p>为1表示当前事务提交方式为自动提交 </p>
<p><img src="/./../images/eb0537e017cd074520bf502c1a6bba9e.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<p>为0表示当前事务提交方式为手动提交 </p>
<h3><span id="提交事务">提交事务</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#提交事务 : 当设置了手动提交方式后，必须有<span class="keyword">commit</span>指令</span><br><span class="line"><span class="keyword">commit</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="回滚事务">回滚事务</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#回滚事务 : 当出现异常，可以回滚事务</span><br><span class="line"><span class="keyword">rollback</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h3><span id="开始事务">开始事务</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#开启事务</span><br><span class="line"><span class="keyword">start</span> transaction;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#开启事务</span><br><span class="line"><span class="keyword">begin</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<h2><span id="四大特性acid">四大特性（ACID）</span></h2><h3><span id="原子性">原子性</span></h3><blockquote>
<p>事务是不可分割的最小操作单位元，要么全部成功，要么全部失败</p>
</blockquote>
<h3><span id="一致性">一致性</span></h3><blockquote>
<p>事务完成时，必须使所有数据都保持一致状态</p>
</blockquote>
<h3><span id="隔离性">隔离性</span></h3><blockquote>
<p>数据库系统提供的隔离机制，保证事务在不受外部并发操作影响到独立环境下运行</p>
</blockquote>
<h3><span id="持久性">持久性</span></h3><blockquote>
<p>事务一旦提交或回滚，它对数据库的改变就是永久的</p>
</blockquote>
<h2><span id="并发事务问题">并发事务问题</span></h2><table>
<thead>
<tr>
<th>问题</th>
<th>描述</th>
</tr>
</thead>
<tbody><tr>
<td>脏读</td>
<td>一个事务读到另外一个事务还没有提交的数据</td>
</tr>
<tr>
<td>不可重复读</td>
<td>一个事务先后读取同一条记录，但两次读取到的数据不同</td>
</tr>
<tr>
<td>幻读</td>
<td>一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了“幻影”</td>
</tr>
</tbody></table>
<h2><span id="事务隔离级别">事务隔离级别</span></h2><blockquote>
<p><strong>事务的隔离级别越高，数据越安全，但是性能越低</strong></p>
</blockquote>
<h3><span id="分类隔离级别从低到高">分类(隔离级别从低到高)：</span></h3><table>
<thead>
<tr>
<th>隔离级别</th>
<th>脏读</th>
<th>不可重复读</th>
<th>幻读</th>
</tr>
</thead>
<tbody><tr>
<td><strong>read uncommitted</strong></td>
<td>Y</td>
<td>Y</td>
<td>Y</td>
</tr>
<tr>
<td><strong>read committed (Oracle默认)</strong></td>
<td>N</td>
<td>Y</td>
<td>Y</td>
</tr>
<tr>
<td><strong>repeatable read (MySQL默认)</strong></td>
<td>N</td>
<td>N</td>
<td>Y</td>
</tr>
<tr>
<td><strong>serialzable</strong></td>
<td>N</td>
<td>N</td>
<td></td>
</tr>
</tbody></table>
<p><strong>注意：</strong> Y表示在当前隔离级别下，当前某种并发事务问题会出现，反之，N表示不会出现</p>
<h3><span id="查看事务隔离级别">查看事务隔离级别：</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#查看事务隔离级别</span><br><span class="line"><span class="keyword">select</span> @<span class="variable">@transaction_isolation</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><img src="/./../images/25e79a2a6d7c8be51af679ac6f37b302.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<h3><span id="设置事务隔离级别">设置事务隔离级别：</span></h3><figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br></pre></td><td class="code"><pre><span class="line">#设置事务隔离级别</span><br><span class="line"><span class="keyword">set</span> [session<span class="operator">|</span><span class="keyword">global</span>] transaction isolation level &#123;read uncommitted <span class="operator">|</span> read committed <span class="operator">|</span> repeatable read<span class="operator">|</span> serializable&#125;;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><strong>eg:</strong></p>
<figure class="highlight sql"><table><tr><td class="gutter"><pre><span class="line">1</span><br><span class="line">2</span><br><span class="line">3</span><br></pre></td><td class="code"><pre><span class="line"><span class="keyword">set</span> session transaction isolation level serializable;</span><br><span class="line"><span class="keyword">commit</span>;</span><br><span class="line"><span class="keyword">select</span> @<span class="variable">@transaction_isolation</span>;</span><br></pre></td></tr></table></figure>

<p><img src="" alt="点击并拖拽以移动"></p>
<p><img src="/./../images/0ffe58a3f92f853373d7476bd3214f63.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<p><strong>注意：</strong></p>
<blockquote>
<p>session: 只对当前客户端窗口有效<br> global:对所有客户端的绘画窗口都有效</p>
</blockquote>
<h3><span id="演示">演示</span></h3><p>打开cmd连接mysql，打开两个窗口进行演示</p>
<p><img src="/./../images/ac0c44e29b5dfbd34a1bad8114cfaaf1.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<h4><span id="脏读">脏读</span></h4><p>一个事务读取到了另外一个事务未提交的数据</p>
<p><img src="/./../images/8765510e33f653e26934eea96425d710.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<h4><span id="不可重复读">不可重复读</span></h4><p>同样的SQL在同一个事务中查询出来的数据不一样</p>
<p><img src="/./../images/77ac2ddb7946d1579cb12dd007c5f0ba.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<h4><span id="可重复读">可重复读</span></h4><p>同样的SQL在同一个事务中查询出来的数据一致</p>
<p><img src="/./../images/3d7ff5a576a06cdb55d45dccc555d5d6.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<h4><span id="幻读"><img src="/./../images/95206a2ea351c0cff54e465f13ff6a32.png" alt="img"><img src="" alt="点击并拖拽以移动"> 幻读</span></h4><p>插入数据的时候提示重复，查询却提示为空</p>
<p><img src="/./../images/870f8d9242415395c11e74a6dc95705e.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<h4><span id="规避幻读serializable">规避幻读(serializable)</span></h4><p>右侧光标闪烁表示堵塞，在等待左侧事务提交了才能继续执行</p>
<p><img src="/./../images/56b09a4902944be08c07af42cc511d5f.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<p><img src="/./../images/a550816af52d19c65c6a518a4c80268f.png" alt="img"><img src="" alt="点击并拖拽以移动"> </p>
<hr>
<p><strong>如有错误，欢迎指正！！！</strong></p>
<p><a target="_blank" rel="noopener" href="https://blog.csdn.net/m0_73569492/article/details/139204460?spm=1001.2014.3001.5501">所有笔记总结目录-CSDN博客</a></p>
</article><div class="post-copyright"><div class="post-copyright__author"><span class="post-copyright-meta"><i class="fas fa-circle-user fa-fw"></i>文章作者: </span><span class="post-copyright-info"><a href="http://xuanskeys.github.io">xuanskeys</a></span></div><div class="post-copyright__type"><span class="post-copyright-meta"><i class="fas fa-square-arrow-up-right fa-fw"></i>文章链接: </span><span class="post-copyright-info"><a href="http://xuanskeys.github.io/2025/04/20/%E6%95%B0%E6%8D%AE%E5%BA%93/">http://xuanskeys.github.io/2025/04/20/%E6%95%B0%E6%8D%AE%E5%BA%93/</a></span></div><div class="post-copyright__notice"><span class="post-copyright-meta"><i class="fas fa-circle-exclamation fa-fw"></i>版权声明: </span><span class="post-copyright-info">本博客所有文章除特别声明外，均采用 <a href="https://creativecommons.org/licenses/by-nc-sa/4.0/" target="_blank">CC BY-NC-SA 4.0</a> 许可协议。转载请注明来源 <a href="http://xuanskeys.github.io" target="_blank">XuanCode</a>！</span></div></div><div class="tag_share"><div class="post-meta__tag-list"><a class="post-meta__tags" href="/tags/1/">1</a></div><div class="post-share"><div class="social-share" data-image="/image/person.jpg" data-sites="facebook,twitter,wechat,weibo,qq"></div><link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/css/share.min.css" media="print" onload="this.media='all'"><script src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/sharejs/dist/js/social-share.min.js" defer></script></div></div><nav class="pagination-post" id="pagination"><a class="pagination-related" href="/2025/04/20/Java%E9%9D%A2%E5%90%91%E5%AF%B9%E8%B1%A1%E7%BC%96%E7%A8%8B/" title="Java面向对象编程"><div class="cover" style="background: var(--default-bg-color)"></div><div class="info"><div class="info-1"><div class="info-item-1">上一篇</div><div class="info-item-2">Java面向对象编程</div></div><div class="info-2"><div class="info-item-1">什么是面向对象编程(OOP)? 它将数据和相关操作封装在对象中，通过对象之间的交互来解决问题。 简单来说，就是世界上所有的物品，都可以是一个对象，对象是一种特殊的数据结构，里面存储物品的相关属性信息，类似于一张表结构。 类就相当于对象的一个模版，结构相同的对象可以使用同一个类来构造   JVM虚拟机JVM是什么? JVM（Java虚拟机）并不是直接安装在操作系统上的一个单独的实体或文件夹，而是一种抽象的计算模型，它通常是由一个程序或者软件包的一部分来实现的。当你在计算机上安装Java运行环境（例如，Java Development Kit, JDK 或者 Java Runtime Environment, JRE）时，实际上就包含了JVM的实现 当你运行一个Java程序时，比如通过命令行输入java...</div></div></div></a><a class="pagination-related" href="/2025/04/20/Oracle/" title="Oracle基本语法"><div class="cover" style="background: var(--default-bg-color)"></div><div class="info text-right"><div class="info-1"><div class="info-item-1">下一篇</div><div class="info-item-2">Oracle基本语法</div></div><div class="info-2"><div class="info-item-1">前言： 1.使用的数据库不同，所使用的语法也略有不同 2.SQL对大小写不敏感 3.Oracle中对引号里面的内容大小写敏感 3.表空间名、文件路径……等需要用单引号将其包含 4.一般引号里面的内容需要大写   准备工作：安装tips：PLSQL、Oracle以及客户端远程连接服务器笔记（仅供参考）-CSDN博客  （1）.Win+R打开services.msc     （2）启动一些服务： （qwq我不知道哪些有用，哪些没用，所以我都把打开了，不知道有没有负面影响，大家参考一下别的博客吧）     登录：1.打开SQL Plus命令行工具第一种方式：  第二种方式： （1）win+R 打开cmd   （2）输入sqlplus    2.以不同用户登录 注意： 1.使用用户口令这种形式登录的时候，是不显示密码的，口令输入的时候是不显示的，直接输就好 2.若是想以显示密码的形式输入，直接在用户名那一块输入：用户名&#x2F;密码 3.超级管理员（sys）输入时需要注意指定 as sysdba  SYSTEM（普通管理员）：...</div></div></div></a></nav><div class="relatedPosts"><div class="headline"><i class="fas fa-thumbs-up fa-fw"></i><span>相关推荐</span></div><div class="relatedPosts-list"><a class="pagination-related" href="/2025/04/20/JUC1/" title="JUC并发编程1(初识进程和线程)"><div class="cover" style="background: var(--default-bg-color)"></div><div class="info text-center"><div class="info-1"><div class="info-item-1"><i class="far fa-calendar-alt fa-fw"></i> 2025-04-20</div><div class="info-item-2">JUC并发编程1(初识进程和线程)</div></div><div class="info-2"><div class="info-item-1"> 初识进程和线程初识进程：定义：  进程（Process）是计算机中的程序关于某数据集合上的一次运行活动，是系统进行资源分配的基本单位，是操作系统结构的基础。在早期面向进程设计的计算机结构中，进程是程序的基本执行实体；在当代面向线程设计的计算机结构中，进程是线程的容器。程序是指令、数据及其组织形式的描述，进程是程序的实体。（百度百科） 进程由程序、数据和进程控制块三部分组成。   什么是进程？ 狭义定义：进程是正在运行的程序的实例。 广义定义：进程是一个具有一定独立功能的程序关于某个数据集合的一次运行活动。它是操作系统动态执行的基本单元，在传统的操作系统中，进程既是基本的分配单元，也是基本的执行单元。   eg：进程可以看做是程序的实例，你可以打开多个程序，每一个程序就是一个进程（比如你重复打开QQ登录不同的用户，每一个用户登录的那个程序就是一个进程）。   如果你关闭一个窗口，那么这个进程也就结束了  概念：1.进程是一个实体。 ...</div></div></div></a><a class="pagination-related" href="/2025/04/20/JUC2/" title="Java并发编程2(锁-Sychronized)"><div class="cover" style="background: var(--default-bg-color)"></div><div class="info text-center"><div class="info-1"><div class="info-item-1"><i class="far fa-calendar-alt fa-fw"></i> 2025-04-20</div><div class="info-item-2">Java并发编程2(锁-Sychronized)</div></div><div class="info-2"><div class="info-item-1">认识Java对象头  32位虚拟机对象头：   64位虚拟机对象头：     1.Mark Word（标记字）:  Mark Word是对象头的一部分，用于存储对象自身的哈希码、GC分代年龄、锁状态标志、线程持有的锁、偏向线程ID（或偏向时间戳）、偏向模式以及锁的状态等信息。 标记字的大小和具体内容可能因JVM实现的不同而有所变化。例如，在64位JVM上，默认情况下Mark Word占用64位（8字节），而在32位JVM上则是32位（4字节）。  2.Class Pointer（类指针）:  这是指向该对象对应类（Class）的指针，通过这个指针可以访问到对象所属类的元数据（如方法表、字段描述等）。类指针的大小依赖于JVM的具体实现及其是否开启了压缩指针（Compressed Oop）选项。 在某些情况下，比如当使用了-XX:+UseCompressedClassPointers选项时，类指针会被压缩以节省内存。  3.Array...</div></div></div></a><a class="pagination-related" href="/2025/04/20/JUC4/" title="Java并发编程4（JUC篇）"><div class="cover" style="background: var(--default-bg-color)"></div><div class="info text-center"><div class="info-1"><div class="info-item-1"><i class="far fa-calendar-alt fa-fw"></i> 2025-04-20</div><div class="info-item-2">Java并发编程4（JUC篇）</div></div><div class="info-2"><div class="info-item-1">本篇文章重点介绍JUC（java.util.concurrent）  JUC是”java.util.concurrent”包的简称，它是Java提供的一个并发工具包，旨在简化多线程编程，提供了丰富的类和接口来帮助开发者更高效、更安全地编写并发程序。JUC包增强了Java对并发的支持，解决了传统多线程编程中的一些难题，如死锁、竞争条件和资源管理等。    原子变量  基本类型原子变量 AtomicInteger  提供对整型值的原子操作，如加法、减法等。 方法示例：incrementAndGet(), decrementAndGet(), addAndGet(int delta), compareAndSet(int expect, int update)。  AtomicLong  类似于AtomicInteger，但是针对长整型（long）值。 方法与AtomicInteger相似，适用于需要处理较大数值的情况。  AtomicBoolean  支持布尔类型的原子操作。 方法示例：get(), set(boolean newValue),...</div></div></div></a><a class="pagination-related" href="/2025/04/20/JUC3/" title="Java并发编程3(CAS)"><div class="cover" style="background: var(--default-bg-color)"></div><div class="info text-center"><div class="info-1"><div class="info-item-1"><i class="far fa-calendar-alt fa-fw"></i> 2025-04-20</div><div class="info-item-2">Java并发编程3(CAS)</div></div><div class="info-2"><div class="info-item-1">Java内存模型(JMM)概念百度百科：java内存模型_百度百科  Java内存模型（Java Memory Model,...</div></div></div></a><a class="pagination-related" href="/2025/04/20/Java%E8%99%9A%E6%8B%9F%E6%9C%BA/" title="Java虚拟机"><div class="cover" style="background: var(--default-bg-color)"></div><div class="info text-center"><div class="info-1"><div class="info-item-1"><i class="far fa-calendar-alt fa-fw"></i> 2025-04-20</div><div class="info-item-2">Java虚拟机</div></div><div class="info-2"><div class="info-item-1">JVM的概念百度百科：java虚拟机 什么是虚拟机？虚拟机是一种抽象化的计算机，通过在实际的计算机上仿真模拟各种计算机功能来实现的。 为什么要有JVM？  Java设计的初衷是使要建的能在任何平台上运行的程序不需要再在每个单独的平台上由程序员进行重写或重编译。 Java虚拟机使这个愿望变为可能，因为它能知道每条指令的长度和平台的其他特性。 JVM的设计目标是提供一个基于抽象规格描述的计算机模型，为解释程序开发人员提供的任何系统上运行。   什么是java虚拟机?JVM全称Java Virtual...</div></div></div></a><a class="pagination-related" href="/2025/04/20/Java%E9%9D%A2%E5%90%91%E5%AF%B9%E8%B1%A1%E7%BC%96%E7%A8%8B/" title="Java面向对象编程"><div class="cover" style="background: var(--default-bg-color)"></div><div class="info text-center"><div class="info-1"><div class="info-item-1"><i class="far fa-calendar-alt fa-fw"></i> 2025-04-20</div><div class="info-item-2">Java面向对象编程</div></div><div class="info-2"><div class="info-item-1">什么是面向对象编程(OOP)? 它将数据和相关操作封装在对象中，通过对象之间的交互来解决问题。 简单来说，就是世界上所有的物品，都可以是一个对象，对象是一种特殊的数据结构，里面存储物品的相关属性信息，类似于一张表结构。 类就相当于对象的一个模版，结构相同的对象可以使用同一个类来构造   JVM虚拟机JVM是什么? JVM（Java虚拟机）并不是直接安装在操作系统上的一个单独的实体或文件夹，而是一种抽象的计算模型，它通常是由一个程序或者软件包的一部分来实现的。当你在计算机上安装Java运行环境（例如，Java Development Kit, JDK 或者 Java Runtime Environment, JRE）时，实际上就包含了JVM的实现 当你运行一个Java程序时，比如通过命令行输入java...</div></div></div></a></div></div><hr class="custom-hr"/><div id="post-comment"><div class="comment-head"><div class="comment-headline"><i class="fas fa-comments fa-fw"></i><span> 评论</span></div></div><div class="comment-wrap"><div><div id="gitalk-container"></div></div></div></div></div><div class="aside-content" id="aside-content"><div class="card-widget card-info text-center"><div class="avatar-img"><img src="/image/person.jpg" onerror="this.onerror=null;this.src='/img/friend_404.gif'" alt="avatar"/></div><div class="author-info-name">xuanskeys</div><div class="author-info-description"></div><div class="site-data"><a href="/archives/"><div class="headline">文章</div><div class="length-num">10</div></a><a href="/tags/"><div class="headline">标签</div><div class="length-num">1</div></a><a href="/categories/"><div class="headline">分类</div><div class="length-num">5</div></a></div><a id="card-info-btn" target="_blank" rel="noopener" href="https://github.com/xuanskeys"><i class="fab fa-github"></i><span>Follow Me</span></a></div><div class="card-widget card-announcement"><div class="item-headline"><i class="fas fa-bullhorn fa-shake"></i><span>公告</span></div><div class="announcement_content">欢迎来到XuanCode。更多详细信息请前往CSDN：https://blog.csdn.net/m0_73569492?type=blog</div></div><div class="sticky_layout"><div class="card-widget" id="card-toc"><div class="item-headline"><i class="fas fa-stream"></i><span>目录</span><span class="toc-percentage"></span></div><div class="toc-content"><ol class="toc"><li class="toc-item toc-level-1"><a class="toc-link"><span class="toc-number">1.</span> <span class="toc-text">数据库基本操作</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">1.1.</span> <span class="toc-text">启动与停止</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">1.1.1.</span> <span class="toc-text">1.第一种方式：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">1.1.2.</span> <span class="toc-text">2.第二种方式:</span></a></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link"><span class="toc-number">2.</span> <span class="toc-text"></span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">2.1.</span> <span class="toc-text">客户端连接</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">2.1.1.</span> <span class="toc-text">1.第一种方式：通过MySQL提供的客户端命令行工具</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">2.1.2.</span> <span class="toc-text">2.第二种方式：通过命令行工具执行命令</span></a></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link"><span class="toc-number">3.</span> <span class="toc-text">SQL</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">3.1.</span> <span class="toc-text">1.DDL(数据定义语言)</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.1.1.</span> <span class="toc-text">数据库操作</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.1.1.1.</span> <span class="toc-text">查询所有数据库:</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.1.1.2.</span> <span class="toc-text">查询当前数据库:</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.1.1.3.</span> <span class="toc-text">创建数据库:</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.1.1.4.</span> <span class="toc-text">删除数据库:</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.1.1.5.</span> <span class="toc-text">使用数据库:</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.1.2.</span> <span class="toc-text">表操作</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.1.2.1.</span> <span class="toc-text">查询：</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">3.1.2.1.1.</span> <span class="toc-text">查询当前数据库所有表：</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">3.1.2.1.2.</span> <span class="toc-text">查询表结构:</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">3.1.2.1.3.</span> <span class="toc-text">查询指定表的建表语句:</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.1.2.2.</span> <span class="toc-text">创建：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.1.2.3.</span> <span class="toc-text">修改：</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">3.1.2.3.1.</span> <span class="toc-text">添加字段：</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">3.1.2.3.2.</span> <span class="toc-text">修改数据类型：</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">3.1.2.3.3.</span> <span class="toc-text">修改表名：</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.1.2.4.</span> <span class="toc-text">删除：</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">3.1.2.4.1.</span> <span class="toc-text">删除表：</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">3.1.2.4.2.</span> <span class="toc-text">删除指定表并重新创建该表：</span></a></li></ol></li></ol></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">3.2.</span> <span class="toc-text">2.DML(数据操作语言)</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.2.1.</span> <span class="toc-text">添加数据（insert）</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.2.1.1.</span> <span class="toc-text">给指定字段添加数据：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.2.1.2.</span> <span class="toc-text">给全部字段添加数据：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.2.1.3.</span> <span class="toc-text">批量添加数据：</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.2.2.</span> <span class="toc-text">修改数据（update）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.2.3.</span> <span class="toc-text">删除数据（delete）</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">3.3.</span> <span class="toc-text">3.DQL(数据查询语言)</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.3.1.</span> <span class="toc-text">编写顺序：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.3.2.</span> <span class="toc-text">基本查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.3.2.1.</span> <span class="toc-text">查询多个字段：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.3.2.2.</span> <span class="toc-text">设置别名：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.3.2.3.</span> <span class="toc-text">去除重复记录：</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.3.3.</span> <span class="toc-text">条件查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.3.4.</span> <span class="toc-text">聚合函数</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.3.5.</span> <span class="toc-text">分组查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.3.6.</span> <span class="toc-text">排序查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.3.7.</span> <span class="toc-text">分页查询</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">3.4.</span> <span class="toc-text">4.DCL(数据控制语言)</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.4.1.</span> <span class="toc-text">管理用户</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.4.1.1.</span> <span class="toc-text">查询用户：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.4.1.2.</span> <span class="toc-text">创建用户：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.4.1.3.</span> <span class="toc-text">修改用户密码：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.4.1.4.</span> <span class="toc-text">删除用户：</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">3.4.2.</span> <span class="toc-text">权限控制</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.4.2.1.</span> <span class="toc-text">查询权限:</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.4.2.2.</span> <span class="toc-text">授予权限:</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">3.4.2.3.</span> <span class="toc-text">撤销权限:</span></a></li></ol></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link"><span class="toc-number">4.</span> <span class="toc-text">函数</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">4.1.</span> <span class="toc-text">查看函数返回结果</span></a></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">4.2.</span> <span class="toc-text">字符串函数</span></a></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">4.3.</span> <span class="toc-text">数值函数</span></a></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">4.4.</span> <span class="toc-text">日期函数</span></a></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">4.5.</span> <span class="toc-text">流程函数</span></a></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link"><span class="toc-number">5.</span> <span class="toc-text">约束</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">5.1.</span> <span class="toc-text">概述</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">5.1.1.</span> <span class="toc-text">概念：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">5.1.2.</span> <span class="toc-text">目的：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">5.1.3.</span> <span class="toc-text">分类：</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">5.2.</span> <span class="toc-text">约束演示</span></a></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">5.3.</span> <span class="toc-text">外键约束</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">5.3.1.</span> <span class="toc-text">概念</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">5.3.2.</span> <span class="toc-text">语法</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">5.3.2.1.</span> <span class="toc-text">添加外键</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">5.3.2.2.</span> <span class="toc-text">删除外键</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">5.3.2.3.</span> <span class="toc-text">指定外键删除&#x2F;更新行为</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">5.3.2.4.</span> <span class="toc-text">example：</span></a></li></ol></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link"><span class="toc-number">6.</span> <span class="toc-text">多表查询</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">6.1.</span> <span class="toc-text">多表关系</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">6.1.1.</span> <span class="toc-text">一对多（多对一）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">6.1.2.</span> <span class="toc-text">多对多（中间表）</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">6.1.3.</span> <span class="toc-text">一对一（单表拆分）</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">6.2.</span> <span class="toc-text">多表查询概述</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">6.2.1.</span> <span class="toc-text">笛卡尔积</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">6.2.2.</span> <span class="toc-text">消除无效笛卡尔积</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">6.3.</span> <span class="toc-text">多表查询分类</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">6.3.1.</span> <span class="toc-text">连接查询</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">6.3.1.1.</span> <span class="toc-text">外连接</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">6.3.1.2.</span> <span class="toc-text">内连接</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">6.3.1.2.1.</span> <span class="toc-text">隐式内连接</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">6.3.1.2.2.</span> <span class="toc-text">显式内连接</span></a></li></ol></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">6.3.1.3.</span> <span class="toc-text">自连接</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">6.3.2.</span> <span class="toc-text">联合查询</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">6.3.3.</span> <span class="toc-text">子查询（嵌套查询）</span></a><ol class="toc-child"><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">6.3.3.0.1.</span> <span class="toc-text">概念</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">6.3.3.0.2.</span> <span class="toc-text">语法</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">6.3.3.0.3.</span> <span class="toc-text">分类</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">6.3.3.0.4.</span> <span class="toc-text">标量子查询（单个值）</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">6.3.3.0.5.</span> <span class="toc-text">列子查询（一列）</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">6.3.3.0.6.</span> <span class="toc-text">行子查询（一行）</span></a></li><li class="toc-item toc-level-5"><a class="toc-link"><span class="toc-number">6.3.3.0.7.</span> <span class="toc-text">表子查询（多行多列）</span></a></li></ol></li></ol></li></ol></li></ol></li><li class="toc-item toc-level-1"><a class="toc-link"><span class="toc-number">7.</span> <span class="toc-text">事务</span></a><ol class="toc-child"><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">7.1.</span> <span class="toc-text">简介（来自百度百科）</span></a></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">7.2.</span> <span class="toc-text">操作（控制事务）</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.2.1.</span> <span class="toc-text">查看&#x2F;设置事务提交方式</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.2.2.</span> <span class="toc-text">提交事务</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.2.3.</span> <span class="toc-text">回滚事务</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.2.4.</span> <span class="toc-text">开始事务</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">7.3.</span> <span class="toc-text">四大特性（ACID）</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.3.1.</span> <span class="toc-text">原子性</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.3.2.</span> <span class="toc-text">一致性</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.3.3.</span> <span class="toc-text">隔离性</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.3.4.</span> <span class="toc-text">持久性</span></a></li></ol></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">7.4.</span> <span class="toc-text">并发事务问题</span></a></li><li class="toc-item toc-level-2"><a class="toc-link"><span class="toc-number">7.5.</span> <span class="toc-text">事务隔离级别</span></a><ol class="toc-child"><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.5.1.</span> <span class="toc-text">分类(隔离级别从低到高)：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.5.2.</span> <span class="toc-text">查看事务隔离级别：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.5.3.</span> <span class="toc-text">设置事务隔离级别：</span></a></li><li class="toc-item toc-level-3"><a class="toc-link"><span class="toc-number">7.5.4.</span> <span class="toc-text">演示</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">7.5.4.1.</span> <span class="toc-text">脏读</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">7.5.4.2.</span> <span class="toc-text">不可重复读</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">7.5.4.3.</span> <span class="toc-text">可重复读</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">7.5.4.4.</span> <span class="toc-text"> 幻读</span></a></li><li class="toc-item toc-level-4"><a class="toc-link"><span class="toc-number">7.5.4.5.</span> <span class="toc-text">规避幻读(serializable)</span></a></li></ol></li></ol></li></ol></li></ol></div></div><div class="card-widget card-recent-post"><div class="item-headline"><i class="fas fa-history"></i><span>最新文章</span></div><div class="aside-list"><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2025/04/20/%E6%9C%80%E7%9F%AD%E8%B7%AF%E9%97%AE%E9%A2%98/" title="最短路问题">最短路问题</a><time datetime="2025-04-20T02:09:44.000Z" title="发表于 2025-04-20 10:09:44">2025-04-20</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2025/04/20/Java%E8%99%9A%E6%8B%9F%E6%9C%BA/" title="Java虚拟机">Java虚拟机</a><time datetime="2025-04-20T02:07:29.000Z" title="发表于 2025-04-20 10:07:29">2025-04-20</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2025/04/20/%E5%9F%BA%E7%A1%80%E7%AE%97%E6%B3%95/" title="基础算法">基础算法</a><time datetime="2025-04-20T02:03:22.000Z" title="发表于 2025-04-20 10:03:22">2025-04-20</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2025/04/20/JUC4/" title="Java并发编程4（JUC篇）">Java并发编程4（JUC篇）</a><time datetime="2025-04-20T02:01:01.000Z" title="发表于 2025-04-20 10:01:01">2025-04-20</time></div></div><div class="aside-list-item no-cover"><div class="content"><a class="title" href="/2025/04/20/JUC3/" title="Java并发编程3(CAS)">Java并发编程3(CAS)</a><time datetime="2025-04-20T01:59:28.000Z" title="发表于 2025-04-20 09:59:28">2025-04-20</time></div></div></div></div></div></div></main><footer id="footer"><div id="footer-wrap"><div class="copyright">&copy;2025 By xuanskeys</div><div class="framework-info"><span>框架 </span><a target="_blank" rel="noopener" href="https://hexo.io">Hexo 7.3.0</a><span class="footer-separator">|</span><span>主题 </span><a target="_blank" rel="noopener" href="https://github.com/jerryc127/hexo-theme-butterfly">Butterfly 5.3.5</a></div></div></footer></div><div id="rightside"><div id="rightside-config-hide"><button id="readmode" type="button" title="阅读模式"><i class="fas fa-book-open"></i></button><button id="darkmode" type="button" title="日间和夜间模式切换"><i class="fas fa-adjust"></i></button><button id="hide-aside-btn" type="button" title="单栏和双栏切换"><i class="fas fa-arrows-alt-h"></i></button></div><div id="rightside-config-show"><button id="rightside-config" type="button" title="设置"><i class="fas fa-cog fa-spin"></i></button><button class="close" id="mobile-toc-button" type="button" title="目录"><i class="fas fa-list-ul"></i></button><a id="to_comment" href="#post-comment" title="前往评论"><i class="fas fa-comments"></i></a><button id="go-up" type="button" title="回到顶部"><span class="scroll-percent"></span><i class="fas fa-arrow-up"></i></button></div></div><div><script src="/js/utils.js"></script><script src="/js/main.js"></script><div class="js-pjax"><script>(() => {
  const isShuoshuo = GLOBAL_CONFIG_SITE.pageType === 'shuoshuo'
  const option = null

  const commentCount = n => {
    const isCommentCount = document.querySelector('#post-meta .gitalk-comment-count')
    if (isCommentCount) {
      isCommentCount.textContent= n
    }
  }

  const initGitalk = (el, path) => {
    if (isShuoshuo) {
      window.shuoshuoComment.destroyGitalk = () => {
        if (el.children.length) {
          el.innerHTML = ''
          el.classList.add('no-comment')
        }
      }
    }

    const gitalk = new Gitalk({
      clientID: '',
      clientSecret: '',
      repo: 'xuanskeys.github.io',
      owner: 'xuanskeys',
      admin: ['xuanskeys'],
      updateCountCallback: commentCount,
      ...option,
      id: isShuoshuo ? path : (option && option.id) || '82b156e8b76b41e2d278534e20737965'
    })

    gitalk.render('gitalk-container')
  }

  const loadGitalk = async(el, path) => {
    if (typeof Gitalk === 'function') initGitalk(el, path)
    else {
      await btf.getCSS('https://cdn.jsdelivr.net/npm/gitalk/dist/gitalk.min.css')
      await btf.getScript('https://cdn.jsdelivr.net/npm/gitalk/dist/gitalk.min.js')
      initGitalk(el, path)
    }
  }

  if (isShuoshuo) {
    'Gitalk' === 'Gitalk'
      ? window.shuoshuoComment = { loadComment: loadGitalk }
      : window.loadOtherComment = loadGitalk
    return
  }

  if ('Gitalk' === 'Gitalk' || !false) {
    if (false) btf.loadComment(document.getElementById('gitalk-container'), loadGitalk)
    else loadGitalk()
  } else {
    window.loadOtherComment = loadGitalk
  }
})()</script></div><script src="https://cdn.bootcss.com/jquery/3.4.1/jquery.min.js"></script><script src="https://cdn.jsdelivr.net/gh/xiabo2/CDN@latest/fishes.js"></script><script id="canvas_nest" defer="defer" color="0,0,255" opacity="0.7" zIndex="-1" count="99" mobile="false" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/dist/canvas-nest.min.js"></script><script id="click-heart" src="https://cdn.jsdelivr.net/npm/butterfly-extsrc/dist/click-heart.min.js" async="async" mobile="false"></script><script async data-pjax src="//busuanzi.ibruce.info/busuanzi/2.3/busuanzi.pure.mini.js"></script><div id="local-search"><div class="search-dialog"><nav class="search-nav"><span class="search-dialog-title">搜索</span><span id="loading-status"></span><button class="search-close-button"><i class="fas fa-times"></i></button></nav><div class="text-center" id="loading-database"><i class="fas fa-spinner fa-pulse"></i><span>  数据加载中</span></div><div class="search-wrap"><div id="local-search-input"><div class="local-search-box"><input class="local-search-box--input" placeholder="搜索文章" type="text"/></div></div><hr/><div id="local-search-results"></div><div id="local-search-stats-wrap"></div></div></div><div id="search-mask"></div><script src="/js/search/local-search.js"></script></div></div></body></html>